package com.mairuide.phone.meet;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;
import com.mairuide.phone.trackevents.TrackEventsParams;

/**
 * 碰面分析Service
 * 
 * @author mh
 *
 */
@Service
@Transactional(readOnly = true)
public class MeetAnalysisService extends BaseServiceProxy<PhoneRecord> {

	@PersistenceContext
	private EntityManager entityManager;

	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Resource
	private BaseSite baseSite;
	
	@Override
	protected BaseRepository<PhoneRecord> getBaseRepository() {
		return null;
	}

	/**
	 * 碰面分析入口
	 * 
	 * @param maParams
	 * @return
	 * @throws ParseException 
	 */
	public List<MeetAnalysis> findMeetPersonInit(MeetAnalysisParams maParams) throws ParseException {
        List<MeetAnalysis> mas = new ArrayList<MeetAnalysis>();
        List<PhoneRecord> prs = findMeetPerson(maParams);
        if (null != prs && !prs.isEmpty()) {
            List<Map<String, Object>> list = new ArrayList<>();//存放所有时间点在浮动时间范围内出现过的手机号码
            for (PhoneRecord prOne : prs) {
                Map<String, Object> meetMap = new HashMap<>();//存放所有时间点在浮动时间范围内出现过的手机号码
                String prOneStartTime = StringUtils.formatDate(prOne.getStartingTime(), "yyyy-MM-dd HH:mm");
                if(meetMap.get(prOneStartTime)==null) {//初始化放入当前号码
                    List<String> l = new ArrayList<String>();
                    l.add(prOne.getServiceNumber());
                    meetMap.put("meetTime", prOneStartTime);
                    meetMap.put("serviceList", l);
                }
                for (PhoneRecord prTwo : prs) {
                    /*********** 判断基站相同、服务号码不相同、日期相同、浮动时间在范围之内 *************/
                    if (prOne.getCid().equals(prTwo.getCid())
                            && prOne.getLac().equals(prTwo.getLac())
                            && !prOne.getServiceNumber().equals(prTwo.getServiceNumber())
                            && facedDate(prOne.getStartingTime(), prTwo.getStartingTime(), maParams.getFloatTime())) {
                        List<String> l = (List<String>)meetMap.get("serviceList");
                        if(!l.contains(prTwo.getServiceNumber())) {
                            l.add(prTwo.getServiceNumber());
                            meetMap.put("serviceList", l);
                            meetMap.put("lac", prTwo.getLac());
                            meetMap.put("cid",prTwo.getCid());
                            meetMap.put("lngb",prTwo.getLngb());
                            meetMap.put("latb",prTwo.getLatb());
                            meetMap.put("address",prTwo.getAddress());
                            list.add(meetMap);
                        }
                    }
                }
                //每一个见面时间点的基站地址

            }
            //筛选出来所有的有效数据
            for(int i=0;i<list.size();i++) {
                Map<String, Object> map = list.get(i);
                List<String> l = (List<String>)map.get("serviceList");
                if(l!=null && l.size() >= maParams.getMeetPersonNum()) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                    //碰面人数条件过滤
                    MeetAnalysis ma = new MeetAnalysis();
                    ma.setMeetDate((String)map.get("meetTime"));//碰面日期
                    ma.setMeetPhone(l);//碰面电话集合
                    ma.setLac((String)map.get("lac"));
                    ma.setCid((String)map.get("cid"));
                    ma.setLatb((String)map.get("latb"));
                    ma.setLngb((String)map.get("lngb"));
                    ma.setAddress((String)map.get("address"));
                    ma.setMeetWeek(StringUtils.getWeekOfDate(sdf.parse((String)map.get("meetTime"))));//碰面周几
                    mas.add(ma);
                }
            }

        }
		return mas;
	}
	
	/**
	 * 计算在浮动时间内的通话
	 * @param prsOneTime
	 * @param prsTwoTime
	 * @param mintes
	 * @return
	 * @throws ParseException
	 */
	private Boolean facedDate(Timestamp prsOneTime, Timestamp prsTwoTime, Integer mintes) throws ParseException {
		if(null == mintes || mintes <= 0) {
			mintes = 5;
		}
		SimpleDateFormat timeformat = new SimpleDateFormat("yyyy-MM-dd,HH:mm:ss");
		long t1 = 0L;
		long t2 = 0L;
		t1 = timeformat.parse(StringUtils.formatDate(prsOneTime, "yyyy-MM-dd,HH:mm:ss")).getTime();
		t2 = timeformat.parse(StringUtils.formatDate(prsTwoTime, "yyyy-MM-dd,HH:mm:ss")).getTime();
		Integer minutes = (int) ((t2 - t1)/(1000*60));
		minutes = Math.abs(minutes);
		if(minutes < mintes) {
			return true;
		}
		return false;
	}

	/**
	 * 碰面分析查询
	 * 
	 * @param eocap
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<PhoneRecord> findMeetPerson(MeetAnalysisParams maParams) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" 
				+ "  TPR.STARTING_TIME AS startingTime,\r\n" 
				+ "  TPR.SERVICE_NUMBER AS serviceNumber,\r\n"
				+ "  TPR.PARTY_NUMBER AS partyNumber,\r\n"
				+ "  TPR.LAC AS lac,\r\n" 
				+ "  TPR.CID AS cid,\r\n"
                + "  TPR.LNGB AS lngb,\r\n"
				+ "  TPR.LATB AS latb,\r\n"
				+ "  TPR.ADDRESS AS address\r\n"
				+ "FROM\r\n"
				+ "  TB_PHONE_RECORDING TPR\r\n" 
				+ "WHERE\r\n"
				+ "TPR.IS_DELETE=0 AND TPR.CALL_TYPE IN(0,1) "
				+ "AND TPR.SERVICE_NUMBER IS NOT NULL AND TPR.PARTY_NUMBER IS NOT NULL "
				+ "AND TPR.LAC IS NOT NULL AND TPR.CID IS NOT NULL ");

		/* 案件id */
		if (StringUtils.isNotEmpty(maParams.getCaseId())) {
			selectSql.append("AND TPR.CASEMANAGE_ID ='" + maParams.getCaseId() + "' ");
		}
		/* 服务号码 */
		selectSql.append(" AND (1=0 ");
		if (StringUtils.isNotEmpty(maParams.getCheckedPhone())) {
			String phones = "";
			for (String phone : maParams.getCheckedPhone().split(",")) {
				if (StringUtils.isNotEmpty(phone)) {
					if (StringUtils.isNotEmpty(phone)) {
						phones = phones + "'" + phone + "',";
					}
				}
			}
			if (phones.endsWith(",")) {
				phones = phones.substring(0, phones.length() - 1);
			}
			if (phones.length() > 0) {
				selectSql.append(" OR TPR.SERVICE_NUMBER IN (" + phones + ")");
			}
		}
		selectSql.append(" ) ");
		if (StringUtils.isNotEmpty(maParams.getStartTime())) {
			selectSql.append(" AND to_char(STARTING_TIME,'yyyy-mm-dd') >= '"+maParams.getStartTime()+"'");
		}
		if (StringUtils.isNotEmpty(maParams.getEndTime())) {
			selectSql.append(" AND to_char(STARTING_TIME,'yyyy-mm-dd') <= '"+maParams.getEndTime()+"'");
		}
		if (StringUtils.isNotEmpty(maParams.getSort())){
            selectSql.append(" ORDER BY "+maParams.getSort()+" "+maParams.getOrder());
        }else {
            selectSql.append(" ORDER BY TPR.STARTING_TIME DESC");
        }

		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PhoneRecord.class));
		// 封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 碰面分析查询地图
	 * @param eocap
	 * @return
	 */
	public Map<String, String> findMeetAnalysisMap(MeetAnalysisParams etp) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" +
				"	LAC,CID,\r\n" +
				"	SERVICE_NUMBER\r\n" +
				"FROM\r\n" +
				"	tb_phone_recording TPR\r\n" +
				"WHERE\r\n" +
				"	TPR.starting_time IS NOT NULL\r\n"+
				"	AND TPR.LAC IS NOT NULL\r\n" +
				"	AND TPR.CID IS NOT NULL\r\n"+
				"   AND TPR.IS_DELETE = 0 AND TPR.CALL_TYPE IN(0,1) ");
		/* 案件id */
		if(StringUtils.isNotEmpty(etp.getCaseId())) {
			selectSql.append("AND TPR.CASEMANAGE_ID ='"+etp.getCaseId()+"' ");
		}
		/* 主叫号码 */
		selectSql.append(" AND (1=0 ");
		if (StringUtils.isNotEmpty(etp.getCheckedPhone())) {
			String phones = "";
			for (String phone : etp.getCheckedPhone().split(",")) {
				if (StringUtils.isNotEmpty(phone)) {
					if (StringUtils.isNotEmpty(phone)) {
						phones = phones + "'" + phone + "',";
					}
				}
			}
			if (phones.endsWith(",")) {
				phones = phones.substring(0, phones.length() - 1);
			}
			if (phones.length() > 0) {
				selectSql.append(" OR TPR.SERVICE_NUMBER IN (" + phones + ")");
			}
		}
		selectSql.append(" ) ");
		/* 通话日期 */
		if(etp.getCallTimes() != null && etp.getCallTimes().size() > 0) {
			int i = 0;
			for(String callTime : etp.getCallTimes()) {
				if(i == 0) {
					selectSql.append(" AND (to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi') = '"+callTime+"' ");
				}else {
					selectSql.append(" OR to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi') = '"+callTime+"' ");
				}
				if(i == etp.getCallTimes().size() - 1) {
					selectSql.append(" ) ");
				}
				i++;
			}
		}

		selectSql.append(" ORDER BY TO_CHAR(TPR.STARTING_TIME,'YYYY-MM-DD') ");
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql.toString());
		Map callInfoByIds = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(etp.getCheckedPhone())&&maps.size()>0){
			callInfoByIds = baseSite.getCallInfoByIds(BaseSite.getBaseSiteIds(maps));
			List<String> baseSiteList = BaseSite.getBaseSiteList(maps);
			List<Map<String,Object>> data = (List<Map<String,Object>>)callInfoByIds.get("data");
			ArrayList<Map<String,Object>> list1 = new ArrayList<>();
			if (data != null && data.size()>0){
				for (Map<String,Object> m:data){
					list1.add(maps.get(baseSiteList.indexOf((String) m.get("id"))));
				}
			}
			callInfoByIds.put("data2",list1);
			//获取查询结果集
		}
		//封装返回数据
		return callInfoByIds;
	}
}
